﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
using System.Collections.Generic;//Please add references
namespace EnterpriseFund.DAL
{
    /// <summary>
    /// 数据访问类:SpecificAdjust
    /// </summary>
    public partial class SpecificAdjust
    {
        public SpecificAdjust()
        { }
        #region  BasicMethod

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(EnterpriseFund.Model.SpecificAdjust model)
        {
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();

            var getBillIdSql = "exec sp_getbillno 'fund.t_SpecificAdjust','FSpecificAdjustNo','','','',@returnBId output ";
            var getBillIdPar = new SqlParameter[]{ 
                new SqlParameter("@returnBId", SqlDbType.NVarChar, 50)
            };

            getBillIdPar[0].Direction = ParameterDirection.Output;

            cmds.CommandText = getBillIdSql.ToString();
            cmds.Parameters = getBillIdPar;
            cmdList.Add(cmds);

            cmds = new CommandInfo();
            strSql = new StringBuilder();

            strSql.Append("insert into fund.t_SpecificAdjust(");
            strSql.Append("FSpecificAdjustNo,FEntityNo,NSRDZDAH,FIssueID,FSpecialAdjustClsNo,FYear,FEnterprisePropNo,FFinancePropNo,FAdjustAmount,FNote,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP,FAllowanceFileEntryID,FStatus)");
            strSql.Append(" values (");
            strSql.Append("@FSpecificAdjustNo,@FEntityNo,@NSRDZDAH,@FIssueID,@FSpecialAdjustClsNo,@FYear,@FEnterprisePropNo,@FFinancePropNo,@FAdjustAmount,@FNote,@FInsEmpNo,@FInsDeptNo,@FInsDate,@FInsIP,@FUpdEmplNo,@FUpdDeptNo,@FUpdDate,@FUpdIP,@FAllowanceFileEntryID,@FStatus)");
            SqlParameter[] parameters = {
					new SqlParameter("@FSpecificAdjustNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@NSRDZDAH", SqlDbType.Decimal,13),
					new SqlParameter("@FIssueID", SqlDbType.NVarChar,50),
					new SqlParameter("@FSpecialAdjustClsNo", SqlDbType.NVarChar,6),
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FEnterprisePropNo", SqlDbType.NVarChar,6),
					new SqlParameter("@FFinancePropNo", SqlDbType.NVarChar,6),
					new SqlParameter("@FAdjustAmount", SqlDbType.Decimal,9),
					new SqlParameter("@FNote", SqlDbType.NVarChar,-1),
					new SqlParameter("@FInsEmpNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FInsDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FInsDate", SqlDbType.DateTime),
					new SqlParameter("@FInsIP", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FUpdDate", SqlDbType.DateTime),
					new SqlParameter("@FUpdIP", SqlDbType.VarChar,15),
					new SqlParameter("@FAllowanceFileEntryID", SqlDbType.Decimal,13),
					new SqlParameter("@FStatus", SqlDbType.NVarChar,1)};
            parameters[0].Direction = ParameterDirection.InputOutput;
            parameters[1].Value = model.FEntityNo;
            parameters[2].Value = model.NSRDZDAH;
            parameters[3].Value = model.FIssueID;
            parameters[4].Value = model.FSpecialAdjustClsNo;
            parameters[5].Value = model.FYear;
            parameters[6].Value = model.FEnterprisePropNo;
            parameters[7].Value = model.FFinancePropNo;
            parameters[8].Value = model.FAdjustAmount;
            parameters[9].Value = model.FNote;
            parameters[10].Value = model.FInsEmpNo;
            parameters[11].Value = model.FInsDeptNo;
            parameters[12].Value = model.FInsDate;
            parameters[13].Value = model.FInsIP;
            parameters[14].Value = model.FUpdEmplNo;
            parameters[15].Value = model.FUpdDeptNo;
            parameters[16].Value = model.FUpdDate;
            parameters[17].Value = model.FUpdIP;
            parameters[18].Value = model.FAllowanceFileEntryID;
            parameters[19].Value = model.FStatus;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTranWithString(cmdList);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(EnterpriseFund.Model.SpecificAdjust model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update fund.t_SpecificAdjust set ");
            strSql.Append("FAdjustAmount=@FAdjustAmount,");
            strSql.Append("FNote=@FNote,");
            strSql.Append("FUpdEmplNo=@FUpdEmplNo,");
            strSql.Append("FUpdDeptNo=@FUpdDeptNo,");
            strSql.Append("FUpdDate=@FUpdDate,");
            strSql.Append("FUpdIP=@FUpdIP,");
            strSql.Append("FSpecialAdjustClsNo=@FSpecialAdjustClsNo,");
            strSql.Append("FStatus=@FStatus,");
            strSql.Append("FIssueID=@FIssueID");
            strSql.Append(" where FSpecificAdjustNo=@FSpecificAdjustNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FAdjustAmount", SqlDbType.Decimal,9),
					new SqlParameter("@FNote", SqlDbType.NVarChar,-1),
					new SqlParameter("@FUpdEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FUpdDate", SqlDbType.DateTime),
					new SqlParameter("@FUpdIP", SqlDbType.VarChar,15),
                    new SqlParameter("@FSpecialAdjustClsNo", SqlDbType.NVarChar,6),
                    new SqlParameter("@FStatus", SqlDbType.NVarChar,1),
					new SqlParameter("@FIssueID", SqlDbType.NVarChar,50),
                    new SqlParameter("@FSpecificAdjustNo", SqlDbType.NVarChar,50)
					};
            parameters[0].Value = model.FAdjustAmount;
            parameters[1].Value = model.FNote;
            parameters[2].Value = model.FUpdEmplNo;
            parameters[3].Value = model.FUpdDeptNo;
            parameters[4].Value = model.FUpdDate;
            parameters[5].Value = model.FUpdIP;
            parameters[6].Value = model.FSpecialAdjustClsNo;
            parameters[7].Value = model.FStatus;
            parameters[8].Value = model.FIssueID;
            parameters[9].Value = model.FSpecificAdjustNo;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public EnterpriseFund.Model.SpecificAdjust GetModel(string FSpecificAdjustNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FSpecificAdjustNo,FEntityNo,NSRDZDAH,FIssueID,FSpecialAdjustClsNo,FYear,FEnterprisePropNo,FFinancePropNo,FAdjustAmount,FNote,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP,FAllowanceFileEntryID from fund.t_SpecificAdjust ");
            strSql.Append(" where FSpecificAdjustNo=@FSpecificAdjustNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FSpecificAdjustNo", SqlDbType.NVarChar,50)			};
            parameters[0].Value = FSpecificAdjustNo;

            EnterpriseFund.Model.SpecificAdjust model = new EnterpriseFund.Model.SpecificAdjust();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        public void Check(Model.SpecificAdjust model)
        {
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append("update fund.t_SpecificAdjust set ");
            strSql.Append("FStatus=@FStatus ");
            strSql.Append(" where FSpecificAdjustNo=@FSpecificAdjustNo ");
            SqlParameter[] parameters4 = {
					new SqlParameter("@FStatus", SqlDbType.NVarChar,6),
					new SqlParameter("@FSpecificAdjustNo", SqlDbType.NVarChar,20)};
            parameters4[0].Value = model.FStatus;
            parameters4[1].Value = model.FSpecificAdjustNo;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters4;
            cmdList.Add(cmds);
            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTranWithString(cmdList);
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public EnterpriseFund.Model.SpecificAdjust DataRowToModel(DataRow row)
        {
            EnterpriseFund.Model.SpecificAdjust model = new EnterpriseFund.Model.SpecificAdjust();
            if (row != null)
            {
                if (row["FSpecificAdjustNo"] != null)
                {
                    model.FSpecificAdjustNo = row["FSpecificAdjustNo"].ToString();
                }
                if (row["FEntityNo"] != null)
                {
                    model.FEntityNo = row["FEntityNo"].ToString();
                }
                if (row["NSRDZDAH"] != null && row["NSRDZDAH"].ToString() != "")
                {
                    model.NSRDZDAH = decimal.Parse(row["NSRDZDAH"].ToString());
                }
                if (row["FIssueID"] != null)
                {
                    model.FIssueID = row["FIssueID"].ToString();
                }
                if (row["FSpecialAdjustClsNo"] != null)
                {
                    model.FSpecialAdjustClsNo = row["FSpecialAdjustClsNo"].ToString();
                }
                if (row["FYear"] != null && row["FYear"].ToString() != "")
                {
                    model.FYear = decimal.Parse(row["FYear"].ToString());
                }
                if (row["FEnterprisePropNo"] != null)
                {
                    model.FEnterprisePropNo = row["FEnterprisePropNo"].ToString();
                }
                if (row["FFinancePropNo"] != null)
                {
                    model.FFinancePropNo = row["FFinancePropNo"].ToString();
                }
                if (row["FAdjustAmount"] != null && row["FAdjustAmount"].ToString() != "")
                {
                    model.FAdjustAmount = decimal.Parse(row["FAdjustAmount"].ToString());
                }
                if (row["FNote"] != null)
                {
                    model.FNote = row["FNote"].ToString();
                }
                if (row["FInsEmpNo"] != null)
                {
                    model.FInsEmpNo = row["FInsEmpNo"].ToString();
                }
                if (row["FInsDeptNo"] != null)
                {
                    model.FInsDeptNo = row["FInsDeptNo"].ToString();
                }
                if (row["FInsDate"] != null && row["FInsDate"].ToString() != "")
                {
                    model.FInsDate = DateTime.Parse(row["FInsDate"].ToString());
                }
                if (row["FInsIP"] != null)
                {
                    model.FInsIP = row["FInsIP"].ToString();
                }
                if (row["FUpdEmplNo"] != null)
                {
                    model.FUpdEmplNo = row["FUpdEmplNo"].ToString();
                }
                if (row["FUpdDeptNo"] != null)
                {
                    model.FUpdDeptNo = row["FUpdDeptNo"].ToString();
                }
                if (row["FUpdDate"] != null && row["FUpdDate"].ToString() != "")
                {
                    model.FUpdDate = DateTime.Parse(row["FUpdDate"].ToString());
                }
                if (row["FUpdIP"] != null)
                {
                    model.FUpdIP = row["FUpdIP"].ToString();
                }
                if (row["FAllowanceFileEntryID"] != null && row["FAllowanceFileEntryID"].ToString() != "")
                {
                    model.FAllowanceFileEntryID = row["FAllowanceFileEntryID"].ToString();
                }
                if (row.Table.Columns.Contains("FFinancePropName"))
                {
                    if (row["FFinancePropName"] != null)
                    {
                        model.FFinancePropName = row["FFinancePropName"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("FFinancePropShortName"))
                {
                    if (row["FFinancePropShortName"] != null)
                    {
                        model.FFinancePropShortName = row["FFinancePropShortName"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("FEnterprisePropName"))
                {
                    if (row["FEnterprisePropName"] != null)
                    {
                        model.FEnterprisePropName = row["FEnterprisePropName"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("NSRBM"))
                {
                    if (row["NSRBM"] != null)
                    {
                        model.NSRBM = row["NSRBM"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("NSRMC"))
                {
                    if (row["NSRMC"] != null)
                    {
                        model.NSRMC = row["NSRMC"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("NSRSBH"))
                {
                    if (row["NSRSBH"] != null)
                    {
                        model.NSRSBH = row["NSRSBH"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("FEmpName"))
                {
                    if (row["FEmpName"] != null)
                    {
                        model.FEmpName = row["FEmpName"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("FSpecialAdjustName"))
                {
                    if (row["FSpecialAdjustName"] != null)
                    {
                        model.FSpecialAdjustName = row["FSpecialAdjustName"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("FIssueNo"))
                {
                    if (row["FIssueNo"] != null)
                    {
                        model.FIssueNo = row["FIssueNo"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("sFStatus"))
                {
                    if (row["sFStatus"] != null)
                    {
                        model.FStatus = row["sFStatus"].ToString();
                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select d.FEmpName,c.FSpecialAdjustName,a.FSpecificAdjustNo,a.FEntityNo,a.NSRDZDAH,a.FIssueID,b.FIssueNo,a.FSpecialAdjustClsNo,a.FYear,a.FEnterprisePropNo,a.FFinancePropNo,a.FAdjustAmount,a.FNote,a.FInsEmpNo,a.FInsDeptNo,a.FInsDate,a.FInsIP,a.FUpdEmplNo,a.FUpdDeptNo,a.FUpdDate,a.FUpdIP,a.FAllowanceFileEntryID ");
            strSql.Append(" FROM fund.t_SpecificAdjust a left join fund.t_AllowanceIssue b on a.FIssueID=b.FIssueID left join fund.t_SpecialAdjustCls c on a.FSpecialAdjustClsNo=c.FSpecialAdjustClsNo and a.FYear=c.FYear left join t_HREmpl d on a.FUpdEmplNo = d.FEmplNo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获得数据查询列表
        /// </summary>
        public DataSet GetQueryList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select *,FSpecificAdjustNo,NSRSBH,NSRBM,NSRMC,FEmpName,FAdjustAmount,FInsDate,FSpecialAdjustName ");
            strSql.Append("  from fund.t_SpecificAdjust join t_HREmpl on t_HREmpl.FEntityNo=fund.t_SpecificAdjust.FEntityNo and FInsEmpNo=FEmplNo join ltais.DJ_NSRXX on fund.t_SpecificAdjust.NSRDZDAH=ltais.DJ_NSRXX.NSRDZDAH join fund.t_SpecialAdjustCls on fund.t_SpecificAdjust.FSpecialAdjustClsNo=fund.t_SpecialAdjustCls.FSpecialAdjustClsNo and fund.t_SpecificAdjust.FYear=fund.t_SpecialAdjustCls.FYear");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" FSpecificAdjustNo,FEntityNo,NSRDZDAH,FIssueID,FSpecialAdjustClsNo,FYear,FEnterprisePropNo,FFinancePropNo,FAdjustAmount,FNote,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP,FAllowanceFileEntryID ");
            strSql.Append(" FROM fund.t_SpecificAdjust ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM fund.v_SpecificAdjust ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.FSpecificAdjustNo desc");
            }
            strSql.Append(")AS Row, T.*  from fund.t_SpecificAdjust T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string FSpecificAdjustNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from fund.t_SpecificAdjust ");
            strSql.Append(" where FSpecificAdjustNo=@FSpecificAdjustNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FSpecificAdjustNo", SqlDbType.NVarChar,50)			};
            parameters[0].Value = FSpecificAdjustNo;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "t_SpecificAdjust";
            parameters[1].Value = "FSpecificAdjustNo";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod
    }
}

